Request: Vision Zero View From Sip Portal

1. VZV needs project id, name, manager, year, vz status, vz type, unit, unit type, nodeid/segmentid, geom

2. Only Completed Vision Zero Projects of current year

3. Database: DOTDEVRHPGSQL01, SIP

4. Tables: SIP_Projects, SIP_Projects_GEO, SIP_Lookup

In [1]:
from ris import db2  #library designed for SQL database connection and querying
from IPython.display import clear_output
import datetime 
from datetime import date
import pandas as pd
import os



clear_output()
timestamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M')
print 'Notebook run: {}'.format(timestamp)
print os.getcwd()
# %load_ext sql 
Notebook run: 2019-08-12 10:18
E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV
In [2]:
#Database connections
#gdb = db2.PostgresDb('dotdevpgsql02', 'GISGRID', quiet = True)
cdb = db2.PostgresDb('DOTDEVRHPGSQL01', 'CRASHDATA', quiet = True)
sdb = db2.PostgresDb('dotpgsql01', 'sip', quiet = True)


#DONE FROM SIP Database
User name (CRASHDATA):soge
Password (CRASHDATA)········
User name (sip):soge
Password (sip)········

Function to the most recent full month at the time of running

In [7]:
#date.today(today.year, today.month, 1) 
#date2 = date(today.year, today.month, 1) - datetime.timedelta(1)
#date2

def mrf_month():
    today = date.today()
    try:
        tm = datetime.datetime(today.year, today.month, today.day+1)
        lim= date(today.year, today.month, 1) - datetime.timedelta(1)
    except ValueError:
        lim= today
    return(str(lim))

mrf_month()
Out[7]:
'2019-07-31'

DataFrame containing all 2019 Sip VZV Projects

This table includes project id, name, manager, year, vz status, vz type, unit, unit type, segmentid, geom

This table only contains projects that are segments

In [8]:
sip_segs = (db2.query_to_table(sdb, """
                
                SELECT distinct* FROM(
                SELECT sp.pid, sp.pjct_name, sp.pm, sp.sip_year, sp.start_date, sp.end_date, spl.description, spg.segmentid, st_setsrid(spg.geom,2263) geom
                FROM public.sip_projects sp
                join public.sip_projects_geo spg
                on sp.pid=spg.pid_fk
                join public.sip_lookup spl
                on sp.vz_status::varchar = spl.lookupid::varchar
                join public.sip_lookup spl2
                on sp.unit::varchar = spl2.lookupid::varchar 
                where sp.status= '15'
                and spl.description != 'None'
                and spg.nodeid=0
                and sp.end_date<='{}'::date 
                order by sp.end_date) sip_segs""".format(mrf_month()))
              )

#print(sip_segs[0])
#print(sip_segs[1])
#sip_segs[0].dtypes
#select inet_server_addr() ip
#select inet_server_port() port

This table only contains projects that are nodes

In [10]:
sip_nodes = (db2.query_to_table(sdb, """
                
                SELECT distinct* FROM(
                SELECT sp.pid, sp.pjct_name, sp.pm, sp.sip_year, sp.start_date, sp.end_date, spl.description, spg.nodeid, st_setsrid(spg.geom,2263) geom
                FROM public.sip_projects sp
                join public.sip_projects_geo spg
                on sp.pid=spg.pid_fk
                join public.sip_lookup spl
                on sp.vz_status::varchar = spl.lookupid::varchar
                join public.sip_lookup spl2
                on sp.unit::varchar = spl2.lookupid::varchar 
                where sp.status= '15'
                and spl.description != 'None'
                and spg.segmentid=0
                and sp.end_date<='{}'::date 
                order by sp.end_date) sip_nodes""".format(mrf_month()))
              )
#print(sip_nodes)
   

Function for Writing dataframes to pgsql using sqlalchemy

In [14]:
from sqlalchemy import create_engine

def df_to_sql(df,tbl_name,db): 
    engine = create_engine('postgresql://{user}:{pw}@10.243.154.88:5432/CRASHDATA'.format(user=db.params['user'],
                                                                                          pw=db.params['password']),
                                                                                          echo=False)
    
    df.to_sql(name='{}'.format(tbl_name), con= engine, if_exists = 'replace', index=False)
    
    db.query("""ALTER TABLE {tbl}
                ALTER COLUMN geom TYPE Geometry USING geom::Geometry;
                
                grant all on {tbl} to public;""".format(tbl=tbl_name))
    
    return tbl_name
    

Writing dataframes to tables in pgsql

In [15]:
segs = df_to_sql(sip_segs,'sip_segs',cdb)

nodes = df_to_sql(sip_nodes,'sip_nodes',cdb)

Exporting pgtables to shape files

In [177]:
db2.pg_shp.export_pg_table_to_shp(r'{path}\sip_shps\segs'.format(path=os.getcwd()),
                                  db,segs, shp_name=segs + '_{mrf}'.format(mrf=datetime.datetime.strptime(mrf_month(), '%Y-%m-%d').strftime("%m%d%Y")))

db2.pg_shp.export_pg_table_to_shp(r'{path}\sip_shps\nodes'.format(path=os.getcwd()),
                                  db,nodes, shp_name=nodes + '_{mrf}'.format(mrf=datetime.datetime.strptime(mrf_month(), '%Y-%m-%d').strftime("%m%d%Y")))
ogr2ogr -overwrite -f "ESRI Shapefile" "C:\Users\soge\Desktop\Jupyter\Requests\AG\SIP_to_VZV\sip_shps\segs\sip_segs_06302019.shp" PG:"host=dotdevpgsql02 user=soge dbname=CRASHDATA password=*********" -sql "select * from public.sip_segs"
Done!
ogr2ogr -overwrite -f "ESRI Shapefile" "C:\Users\soge\Desktop\Jupyter\Requests\AG\SIP_to_VZV\sip_shps\nodes\sip_nodes_06302019.shp" PG:"host=dotdevpgsql02 user=soge dbname=CRASHDATA password=*********" -sql "select * from public.sip_nodes"
Done!
In [16]:
from IPython.display import Image
PATH = "C:\Users\soge\Desktop\Jupyter\Requests\AG\SIP_to_VZV"
Image(filename = PATH + "\SIP_to_VZV701.png", width=1000, height=1000)
Out[16]:
In [15]:
cdb.query( """DROP TABLE if exists {segs};
             DROP TABLE if exists {nodes};""".format(segs=segs, nodes=nodes))
Out[15]:
output(data=None, columns=None, desc=None)
In [5]:
from ris import pg_import_export_shps as shp 

shp.import_shp_to_pg('E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV\VZV_shps\SIP_Corridors.shp', cdb, schema='public', precision=False, 
                     permission=True, gdal_data=r"C:\Program Files (x86)\GDAL\gdal-data") 
Query Failed:

	comment on table public."sip_corridors" is 'sip_corridors created by soge on 2019-08-01 18:27  - imported using ris module vesion 1.3.9'
An exception has occurred, use %tb to see the full traceback.

SystemExit
In [7]:
shp.import_shp_to_pg('E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV\VZV_shps\SIP_Intersections.shp', cdb, schema='public', precision=False, 
                     permission=True, gdal_data=r"C:\Program Files (x86)\GDAL\gdal-data") 
Indexing...
In [17]:
sip_corr = (db2.query_to_table(ddb, """
                
                SELECT *
                FROM public.sip_projects sp
                join public.sip_projects_geo spg
                on sp.pid=spg.pid_fk
                where sip_year > 2008 and sip_year<2014
                and spg.nodeid=0
                order by sip_year;""")
              )

sip_itx = (db2.query_to_table(ddb, """
                
                SELECT *
                FROM public.sip_projects sp
                join public.sip_projects_geo spg
                on sp.pid=spg.pid_fk
                where sip_year > 2008 and sip_year<2014
                and spg.segmentid=0
                order by sip_year;""")
              )
In [18]:
corr = df_to_sql(sip_corr,'sip_corr',cdb)

itx = df_to_sql(sip_itx,'sip_itx',cdb)
C:\Python27\ArcGIS10.4\lib\site-packages\sqlalchemy\dialects\postgresql\base.py:2972: SAWarning: Did not recognize type 'geometry' of column 'geom'
  "Did not recognize type '%s' of column '%s'" % (attype, name)
In [34]:
sip_itx_ints = (db2.query_to_table(cdb,"""
                select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm, 
                "SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm, 
                sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ
                from working.sip_corridors vzv
                join sip_corr sip 
                on st_dwithin(st_setsrid(vzv."geometry",2263), st_setsrid(sip.geom,2263),65) 
                where "SIP_YR" between 2009 and 2013"""))"""))
           
In [28]:
sip_itx_ints.to_csv("Sip_to_VZV_Itx_Overlap-{}.csv".format(datetime.datetime.now().strftime('%Y-%m-%d')),index=False) 

MAP OF INTERSECTION OVERLAPS

In [82]:
from IPython.display import Image
PATH = "E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV"
Image(filename = PATH + "\Sip_to_vzv_itx_overlap-812.png", width=1000, height=1000)
Out[82]:
In [ ]:
sip_corr_ints = (db2.query_to_table(cdb,"""
                select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm, 
                "SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm, 
                sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ
                from working.sip_corridors vzv
                join sip_corr sip 
                on st_dwithin(st_setsrid(vzv."geometry",2263), st_setsrid(sip.geom,2263),65) 
                where "SIP_YR" between 2009 and 2013"""))
In [ ]:
sip_corr_ints.to_csv("Sip_to_VZV_Corr_Overlap-{}.csv".format(datetime.datetime.now().strftime('%Y-%m-%d')),index=False) 

MAP OF CORRIDOR OVERLAPS

In [51]:
from IPython.display import Image
PATH = "E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV"
Image(filename = PATH + "\Sip_to_zzv_corr_overlap-812.png", width=1000, height=1000)
Out[51]:

QUERY FOR COMPLETE OVERLAP

In [92]:
CO = (db2.query_to_table(cdb,"""
            --COMPLETE OVERLAP
            select distinct sip_pid, sip_pjct_name, sip_year, sip_pm, vzv_sip_id, vzv_pjct_name, vzv_sip_year, vzv_pm, typ
            from (
            select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm, 
            "SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm, 
            sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ
            from working.sip_corridors vzv
            join sip_corr sip
            on st_dwithin(st_setsrid(vzv."geometry",2263), st_setsrid(sip.geom,2263),300)
            where  "SIP_YR" between 2009 and 2013


            union all

            select sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm, 
            vzv.sip_id vzv_sip_id, vzv.pjct_name vzv_pjct_name, vzv.sip_yr vzv_sip_year, vzv.pm vzv_pm, 
            sip.nodeid sip_geomid, vzv.nodeid_1 vzv_geomid, 'ITX' typ
            from sip_intersections vzv
            join sip_itx sip
            on st_dwithin(st_setsrid(vzv.wkb_geometry,2263), st_setsrid(sip.geom,2263),300)
            where vzv.sip_yr between 2009 and 2013

            )corr_itx"""))
In [94]:
CO.to_csv('SIP_VZV_matches-812.csv')

MAP OF COMPLETE OVERLAP

In [83]:
from IPython.display import Image
PATH = "E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV"
Image(filename = PATH + "\complete_overlap-812.png", width=1000, height=1000)
Out[83]:
In [18]:
sip_vzv_overlap[['sip_pjct_name','vzv_pjct_name','sip_year','vzv_sip_year','typ']].to_csv('sip_vzv_pjctss.csv')

SIP IDS that don't HAVE a VZV match

In [84]:
sip = (db2.query_to_table(cdb,"""
        select * from (
        --Sip CORR PIDS that don't have a VZV match
        select distinct x.sip_pid, x.sip_pjct_name, x.sip_year, x.sip_pm, x.typ  
        from (select distinct pid sip_pid, pjct_name sip_pjct_name, sip_year sip_year, pm sip_pm, segmentid sip_geomid, 'CORR' typ from sip_corr) x

        left join (select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm, 
               "SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm, 
               sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ
               from working.sip_corridors vzv
               join sip_corr sip 
               on st_dwithin(st_setsrid(vzv."geometry",2263), st_setsrid(sip.geom,2263),65) 
               where "SIP_YR" between 2009 and 2013) y

        on x.sip_geomid = y.sip_geomid
        where vzv_sip_id is null
        order by x.sip_pjct_name
        ) corrs

        union 

        select * from (
        --Sip ITX PIDS that don't have a VZV match
        select x.sip_pid, x.sip_pjct_name, x.sip_year, x.sip_pm, x.typ
        from (select distinct pid sip_pid, pjct_name sip_pjct_name, sip_year sip_year, pm sip_pm, nodeid sip_geomid, 'ITX' typ from sip_itx) x

        left join (select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm, 
               vzv.sip_id vzv_sip_id, vzv.pjct_name vzv_pjct_name, vzv.sip_yr vzv_sip_year, vzv.pm vzv_pm, 
               sip.nodeid sip_geomid, vzv.nodeid_1 vzv_geomid, 'ITX' typ
               from sip_intersections vzv
               join sip_itx sip
               on st_dwithin(st_setsrid(vzv.wkb_geometry,2263), st_setsrid(sip.geom,2263),300)
               where vzv.sip_yr between 2009 and 2013) y
               on x.sip_geomid = y.sip_geomid
        where vzv_sip_id is null
        order by x.sip_pjct_name
        ) itxs

        """))
In [91]:
sip.to_csv('SIP_unmatched-812.csv')

VZV PIDS that don't have a SIP match

In [87]:
vzv= (db2.query_to_table(cdb,"""

        
        select * from (
        --VZV CORR PIDS that don't have a SIP match
        select distinct x.vzv_sip_id, x.vzv_pjct_name, x.vzv_sip_year, x.vzv_pm, x.typ
        from (select distinct "SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm,  'CORR' typ, st_setsrid("geometry",2263) vzv_geom 
              from working.sip_corridors where "SIP_YR" between 2009 and 2013) x

        left join (select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm, 
                   "SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm, 
               sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ, st_setsrid(vzv."geometry",2263) vzv_geom
               from working.sip_corridors vzv
               join sip_corr sip 
               on st_dwithin(st_setsrid(vzv."geometry",2263), st_setsrid(sip.geom,2263),65) 
               where "SIP_YR" between 2009 and 2013) y
        on x.vzv_geom = y.vzv_geom
        where y.sip_pid is null
        order by x.vzv_sip_id
        ) corrs

        union 

        
        select * from (
        --VZV ITX  PIDS that don't have a SIP match
        select distinct x.vzv_sip_id, x.vzv_pjct_name, x.vzv_sip_year, x.vzv_pm, x.typ
        from (select distinct sip_id vzv_sip_id, pjct_name vzv_pjct_name, sip_yr vzv_sip_year, pm vzv_pm, 'ITX' typ, nodeid_1 geom
              from sip_intersections where sip_yr between 2009 and 2013) x

        left join (select sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm, 
               vzv.sip_id vzv_sip_id, vzv.pjct_name vzv_pjct_name, vzv.sip_yr vzv_sip_year, vzv.pm vzv_pm, 
               sip.nodeid sip_geomid, vzv.nodeid_1 vzv_geomid, 'ITX' typ
               from sip_intersections vzv
               join sip_itx sip
               on st_dwithin(st_setsrid(vzv.wkb_geometry,2263), st_setsrid(sip.geom,2263),300)
               where vzv.sip_yr between 2009 and 2013) y
        on x.geom = y.vzv_geomid
        where y.sip_pid is null
        order by x.vzv_sip_id
        ) itxs """))
In [90]:
vzv.to_csv('VZV_unmatched-812.csv')